"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : GSC功能模块
Case Name   : 线程池模式下连接数据库，gs_lsc_memory_detail统计信息验证
Create At   : 2023/5/24
Owner       : @peilinqian
Description :
    step1:修改enable_global_syscache为on;enable_thread_pool为on;
    step2:重启数据库，使参数生效;
    step3:创建数据库;
    step4:创建表并查询;
    step5:查询所有库并进行连接;
    step6:以线程开启事务，步骤7并发session开启前、开启中、关闭后中分别查询总的本地缓存;
    step7:多并发进行建立连接并保持10s;
Expect      :
    step1:修改enable_global_syscache为on;enable_thread_pool为on; expect:成功
    step2:重启数据库，使参数生效; expect:重启成功
    step3:创建数据库; expect:创建成功
    step4:创建表并查询; expect:操作成功
    step5:查询所有库并进行连接; expect:连接成功
    step6:以线程开启事务，步骤7并发session开启前、开启中、关闭后中分别查询总的本地缓存;
        expect: 步骤7session开启中缓存大于开启前和关闭后的缓存
    step7:多并发进行建立连接并保持10s; expect:操作成功
History     :
"""
import os
import time
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.com = Common()
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.db_name = 'db_gsc0048'
        self.tb_name = 'tb_gsc0048'

    def test_main(self):
        step_txt = '----step0:查看enable_global_syscache初始配置值;----'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param('enable_global_syscache')
        step_txt = '----step0:查看enable_thread_pool初始值;----'
        self.log.info(step_txt)
        self.init_para2 = self.com.show_param('enable_thread_pool')

        step_txt = '----step1:修改enable_global_syscache为on;' \
                   'enable_thread_pool为on; expect:成功----'
        self.log.info(step_txt)
        msg = self.pri_sh.execute_gsguc('set',
                                        self.constant.GSGUC_SUCCESS_MSG,
                                        f"enable_global_syscache= on")
        self.assertTrue(msg, '执行失败:' + step_txt)
        msg = self.pri_sh.execute_gsguc('set',
                                        self.constant.GSGUC_SUCCESS_MSG,
                                        f"enable_thread_pool= on")
        self.assertTrue(msg, '执行失败:' + step_txt)

        step_txt = '----step2:重启数据库，使参数生效; expect:重启成功----'
        self.log.info(step_txt)
        restart_result = self.pri_sh.restart_db_cluster()
        self.assertTrue(restart_result)
        self.log.info('----查询参数----')
        self.new_para1 = self.com.show_param('enable_global_syscache')
        self.assertEqual(self.new_para1, 'on', '执行失败:' + step_txt)
        self.new_para2 = self.com.show_param('enable_thread_pool')
        self.assertEqual(self.new_para2, 'on', '执行失败:' + step_txt)

        step_txt = f'----step3:创建数据库; expect:创建成功----'
        self.log.info(step_txt)
        create_db = f'drop database if exists {self.db_name};' \
            f'create database {self.db_name};'
        create_result = self.pri_sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step4:创建表并查询; expect:操作成功----'
        self.log.info(step_txt)
        create_tb_sql = f"drop table if exists {self.tb_name};" \
            f"create table {self.tb_name} " \
            f"(c_integer integer,c_tinyint tinyint," \
            f"c_smallint smallint,c_binary_integer binary_integer," \
            f"c_bigint bigint,c_numeric numeric(10,4)," \
            f"c_number number(10,4),c_smallserial smallserial);"
        create_result = self.pri_sh.execut_db_sql(create_tb_sql,
                                                  dbname=f'{self.db_name}')
        self.log.info(create_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step5:查询所有库并进行连接; expect:连接成功----'
        self.log.info(step_txt)
        select_db_sql = "select datname from pg_database where datname " \
                        "not like('template%');"
        select_db_result = self.pri_sh.execut_db_sql(select_db_sql)
        self.log.info(select_db_result)
        db_list = select_db_result.splitlines()[2:-1:1]
        for i in db_list:
            con_result = self.pri_sh.execut_db_sql(
                'select current_database();',
                dbname=f'{i}')
            self.log.info(con_result)
            self.assertIn(i, con_result, '执行失败:' + step_txt)

        step6_txt = '----step6:以线程开启事务，步骤7并发session开启前、开启中、关闭后中分别查询总的本地缓存; ' \
                    'expect: 步骤7session开启中缓存大于开启前和关闭后的缓存----'
        self.log.info(step6_txt)
        check_sql = 'select * from pg_sleep(1);' \
            f'select sum(totalsize) as sumtotal,sum(usedsize) as sumused ' \
            f'from gs_lsc_memory_detail;' \
            f'select * from pg_sleep(5);' \
            f'select sum(totalsize) as sumtotal,sum(usedsize) as sumused ' \
            f'from gs_lsc_memory_detail;' \
            f'select * from pg_sleep(15);' \
            f'select sum(totalsize) as sumtotal,sum(usedsize) as sumused ' \
            f'from gs_lsc_memory_detail;'
        session1 = ComThread(self.pri_sh.execut_db_sql,
                             args=(check_sql, '', self.db_name,))
        session1.setDaemon(True)
        session1.start()
        time.sleep(2)

        step_txt = '----step7:多并发进行建立连接并保持10s; expect:操作成功----'
        self.log.info(step_txt)
        connect_thread = []
        self.circle_num = 15
        check_sql = f"select * from pg_sleep(10);"
        for j in range(self.circle_num):
            connect_thread.append(ComThread(self.pri_sh.execut_db_sql, args=(
                check_sql, '', self.db_name,)))
            connect_thread[j].setDaemon(True)
            connect_thread[j].start()
        for j in range(self.circle_num):
            connect_thread[j].join(20)
            result = connect_thread[j].get_result()
            self.log.info(result)
            self.assertIn('1 row', result, '执行失败:' + step_txt)

        step_txt = '----步骤6、7断言----'
        self.log.info(step_txt)
        session1.join()
        session1_result = session1.get_result()
        self.log.info(session1_result)
        session1_total1 = int(session1_result.splitlines()[7].split('|')[0])
        session1_used1 = int(session1_result.splitlines()[7].split('|')[1])
        session1_total2 = int(session1_result.splitlines()[17].split('|')[0])
        session1_used2 = int(session1_result.splitlines()[17].split('|')[1])
        session1_total3 = int(session1_result.splitlines()[27].split('|')[0])
        session1_used3 = int(session1_result.splitlines()[27].split('|')[1])
        self.assertGreater(session1_total2, session1_total1,
                           '执行失败:' + step6_txt)
        self.assertGreater(session1_used2, session1_used1,
                           '执行失败:' + step6_txt)
        self.assertGreater(session1_total2, session1_total3,
                           '执行失败:' + step6_txt)
        self.assertGreater(session1_used2, session1_used3,
                           '执行失败:' + step6_txt)

    def tearDown(self):
        step_txt = '----this is teardown----'
        self.log.info(step_txt)
        step1_txt = '----清理数据库; expect:清理成功----'
        self.log.info(step1_txt)
        drop_cmd = f"drop database if exists {self.db_name};"
        drop_result = self.pri_sh.execut_db_sql(drop_cmd)
        self.log.info(drop_result)

        step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
        self.log.info(step2_txt)
        msg1 = self.pri_sh.execute_gsguc('set',
                                         self.constant.GSGUC_SUCCESS_MSG,
                                         f"enable_global_syscache="
                                         f"{self.init_para1}")
        msg2 = self.pri_sh.execute_gsguc('reload',
                                         self.constant.GSGUC_SUCCESS_MSG,
                                         f"enable_thread_pool="
                                         f"{self.init_para2}")
        step3_txt = '----重启数据库，使参数生效; expect:重启成功----'
        self.log.info(step3_txt)
        restart_result = self.pri_sh.restart_db_cluster()
        step4_txt = '----查询数据库状态; expect:状态正常----'
        self.log.info(step4_txt)
        status_result = self.pri_sh.get_db_cluster_status('status')

        step_txt = '----teardown断言----'
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_result,
                      "执行失败" + step_txt + step1_txt)
        self.assertTrue(msg1, '执行失败:' + step_txt + step2_txt)
        self.assertTrue(msg2, '执行失败:' + step_txt + step2_txt)
        self.assertTrue(restart_result, '执行失败:' + step_txt + step3_txt)
        self.assertTrue(status_result, '执行失败:' + step_txt + step4_txt)

        self.log.info(f'----{os.path.basename(__file__)}:end----')
